/*
封装导出excel的方法
*/
import * as XLSX from "xlsx";
import XLSXS from "xlsx-js-style";
import { saveAs } from "file-saver";
import XSU from "./xlsx-styleUtils";
// 自动列宽计算
const AutoWidth = (ws, arr) => {
  // 最小列宽
  let minWch = 10;
  // 有地址信息时使用的最小列宽
  let addressInfoMinWch = 50;
  // 设置worksheet每列的最大宽度
  const colWidth = arr.map((row) =>
    row.map((val) => {
      // 判断是否为null/undefined
      if (val == null) {
        return { wch: minWch };
      } else if (val.toString().charCodeAt(0) > 255) {
        // 判断是否为中文
        if (val.indexOf("地址") > 0) {
          return { wch: addressInfoMinWch + val.toString().length * 2 };
        } else {
          return { wch: minWch + val.toString().length * 2 };
        }
      } else {
        return { wch: minWch + val.toString().length };
      }
    })
  );
  // 以第一行为初始值
  const result = colWidth[0];
  for (let i = 1; i < colWidth.length; i++) {
    for (let j = 0; j < colWidth[i].length; j++) {
      if (result[j].wch < colWidth[i][j].wch) {
        result[j].wch = colWidth[i][j].wch;
      }
    }
  }
  ws["!cols"] = result;
};

// 数组转换成JSON
const formatJSON = (key, data) => {
  return data.map((v) =>
    key.map((i) => {
      return v[i];
    })
  );
};

// 字符串转ArrayBuffer
const s2ab = (s) => {
  const buf = new ArrayBuffer(s.length);
  const view = new Uint8Array(buf);
  for (let i = 0; i !== s.length; ++i) view[i] = s.charCodeAt(i) & 0xff;
  return buf;
};

// 导出EXCEL表格
export const exportDataToExcel = ({
  importNotice, //导入须知
  headerKey, // 表头名数组
  requiredHeader, // 必填项的表头名数组
  requiredColor = "FFFF0000", //必填项的表头字体颜色(默认为红色)
  data, // 需要导出数据的数组
  fileName, // 导出文件名
} = {}) => {
  // 创建Workbook对象
  const wb = XLSX.utils.book_new();
  const arr = formatJSON(headerKey, data);
  fileName = fileName || "excel-list";

  if (headerKey?.length) arr.unshift(headerKey);
  if (importNotice?.length) arr.unshift(importNotice);
  // 将数组数据转换为worksheet
  const ws = XLSX.utils.aoa_to_sheet(arr);

  if (importNotice?.length) {
    arr.shift();
    AutoWidth(ws, arr);
    const rows = importNotice.map((mi) => mi.length)[0];
    ws["!rows"] = [{ hpx: rows * 0.75 }];
  } else {
    AutoWidth(ws, arr);
  }
  // 向Workbook对象中追加worksheet和fileName
  XLSX.utils.book_append_sheet(wb, ws, fileName);

  // 调用样式处理方法
  const wbStyle = utilsTest(wb, importNotice, requiredHeader, requiredColor);

  // 生成EXCEL的配置项
  // 这里调用XLSXS 来调整excel样式
  const wbout = XLSXS.write(wbStyle, {
    bookType: "xlsx",
    bookSST: false,
    type: "binary",
  });
  // 浏览器下载
  saveAs(
    new Blob([s2ab(wbout)], {
      type: "application/octet-stream",
    }),
    `${fileName}.xlsx`
  );
};

// 表格样式编辑
const utilsTest = (wb, importNotice, requiredHeader, requiredColor) => {
  const excelContent = wb.Sheets[wb.SheetNames[0]];

  XSU.setAlignmentWrapTextAll(wb, wb.SheetNames[0], true);
  XSU.setFontTypeAll(wb, wb.SheetNames[0], "等线");
  XSU.setAlignmentVerticalAll(wb, wb.SheetNames[0], "center");
  XSU.setFontSizeAll(wb, wb.SheetNames[0], 12);

  for (let key in excelContent) {
    if (key.indexOf("!") < 0) {
      // 处理导入须知的表格样式
      if (importNotice?.length && key == "A1") {
        const colMerges = XSU.getMaxCol(wb, wb.SheetNames[0]);
        const merges = [{ s: { c: 0, r: 0 }, e: { c: colMerges - 1, r: 0 } }];
        XSU.mergeCellsByObj(wb, wb.SheetNames[0], merges);
        XSU.setAlignmentHorizontal(wb, wb.SheetNames[0], key, "left");
      } else {
        XSU.setAlignmentHorizontal(wb, wb.SheetNames[0], key, "center");
      }
      // 处理必填项表格头数组的字体颜色
      if (
        requiredHeader?.length &&
        requiredHeader.includes(excelContent[key].v)
      ) {
        XSU.setFontColorRGB(wb, wb.SheetNames[0], key, requiredColor);
      }
      XSU.setBorderStyles(wb, wb.SheetNames[0], key, XSU.borderAll);
    }
  }
  return wb;
};

// 从Excel文件中获取表格头
const getHeaderRow = (sheet) => {
  const headers = [];
  // 将 A1:G8 这种字符串转换为行列对象
  const range = XLSX.utils.decode_range(sheet["!ref"]);
  let C;
  const R = range.s.r;
  // 从第一列开始，遍历范围中的每一列
  for (C = range.s.c; C <= range.e.c; ++C) {
    // 将行列对象转换为 A1 这种字符串
    const cell = sheet[XLSX.utils.encode_cell({ c: C, r: R })];
    // 用默认值替换
    let hdr = "UNKNOWN " + C;
    if (cell && cell.t) hdr = XLSX.utils.format_cell(cell);
    headers.push(hdr);
  }
  return headers;
};

// 读取Excel文件
export const readDataFromExcel = (data, type, outputType) => {
  // 读取Excel文件并保存到Workbook对象
  const workbook = XLSX.read(data, { type: type });
  const firstSheetName = workbook.SheetNames[0]; // 取第一张表
  // 获取Workbook对象的worksheet
  const worksheet = workbook.Sheets[firstSheetName];
  const header = getHeaderRow(worksheet);
  // 将worksheet转化成数组
  const results = XLSX.utils.sheet_to_json(worksheet, outputType || null);
  return { header, results };
};

export default {
  exportDataToExcel,
  readDataFromExcel,
};
